import sqlite3
import os
from contextlib import contextmanager


class DatabaseManager:
    """统一的数据库管理类"""
    
    def __init__(self, db_path='face_database.db'):
        self.db_path = db_path
        self.init_database()
    
    def init_database(self):
        """初始化数据库表"""
        with self.get_connection() as conn:
            c = conn.cursor()
            
            # 创建人脸数据表
            c.execute('''CREATE TABLE IF NOT EXISTS faces (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            name TEXT NOT NULL UNIQUE,
                            face_data BLOB NOT NULL,
                            created_at TEXT DEFAULT (datetime('now','localtime'))
                        )''')
            
            # 创建考勤表
            c.execute('''CREATE TABLE IF NOT EXISTS attendance (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            name TEXT NOT NULL,
                            action TEXT CHECK(action IN ('in','out')) NOT NULL,
                            ts TEXT DEFAULT (datetime('now','localtime'))
                        )''')
            
            conn.commit()
    
    @contextmanager
    def get_connection(self):
        """获取数据库连接的上下文管理器"""
        conn = None
        try:
            conn = sqlite3.connect(self.db_path)
            yield conn
        except Exception as e:
            if conn:
                conn.rollback()
            raise e
        finally:
            if conn:
                conn.close()
    
    def save_face_data(self, name, face_data_bytes):
        """保存人脸数据"""
        with self.get_connection() as conn:
            c = conn.cursor()
            c.execute("""
                INSERT INTO faces (name, face_data, created_at)
                VALUES (?, ?, datetime('now','localtime'))
                ON CONFLICT(name) DO UPDATE SET
                    face_data=excluded.face_data,
                    created_at=excluded.created_at
            """, (name, face_data_bytes))
            conn.commit()
    
    def get_all_faces(self):
        """获取所有人脸数据"""
        with self.get_connection() as conn:
            c = conn.cursor()
            c.execute("SELECT name, face_data FROM faces")
            return c.fetchall()
    
    def get_face_list(self):
        """获取人脸列表（不包含二进制数据）"""
        with self.get_connection() as conn:
            c = conn.cursor()
            c.execute("SELECT name, created_at FROM faces ORDER BY created_at DESC")
            return c.fetchall()
    
    def insert_attendance(self, name, action):
        """插入考勤记录"""
        if action not in ['in', 'out']:
            raise ValueError("action must be 'in' or 'out'")
        
        with self.get_connection() as conn:
            c = conn.cursor()
            c.execute("""
                INSERT INTO attendance (name, action, ts)
                VALUES (?, ?, datetime('now','localtime'))
            """, (name, action))
            conn.commit()
    
    def get_attendance_records(self, limit=50):
        """获取考勤记录"""
        with self.get_connection() as conn:
            c = conn.cursor()
            c.execute("SELECT name, action, ts FROM attendance ORDER BY ts DESC LIMIT ?", (limit,))
            return c.fetchall()
    
    def get_person_attendance_records(self, name, days=30):
        """获取指定人员的考勤记录"""
        with self.get_connection() as conn:
            c = conn.cursor()
            c.execute("""
                SELECT name, action, ts FROM attendance 
                WHERE name = ? AND ts >= datetime('now', '-{} days', 'localtime')
                ORDER BY ts DESC
            """.format(days), (name,))
            return c.fetchall()
    
    def get_attendance_statistics(self, days=30):
        """获取考勤统计信息"""
        with self.get_connection() as conn:
            c = conn.cursor()
            # 获取指定天数内的考勤统计
            c.execute("""
                SELECT 
                    name,
                    COUNT(CASE WHEN action = 'in' THEN 1 END) as clock_in_count,
                    COUNT(CASE WHEN action = 'out' THEN 1 END) as clock_out_count,
                    MAX(ts) as last_attendance
                FROM attendance 
                WHERE ts >= datetime('now', '-{} days', 'localtime')
                GROUP BY name
                ORDER BY last_attendance DESC
            """.format(days))
            return c.fetchall()


# 全局数据库管理器实例
db_manager = DatabaseManager()